#include "SQLiteManager.h"
#include <string.h>
int create_database()
{
	if( sqlite3_open(filename,&ppDb) < 0){
		return -1;
	}else{
    	printf("%s,open success!\n",filename);
		return 0;
	}
}


int create_table()
{
	sprintf(sql,"%s%s","create table employ_manager","(department char,id int,\
	   name char,age int, sex char, year int,salary int,manager int ,passwd char);");
	if(sqlite3_exec(ppDb,sql,NULL,NULL,&errmsg) < 0){
		printf("Sqlite3 create employ_manager fail!Reason: %s .\n",errmsg);
		return -1;
	}else{
		printf("sqlite3 create  employ_manager success!\n");
	}

	sprintf(sql,"%s%s","create table history","(action char,data char, name char);");
	if(sqlite3_exec(ppDb,sql,NULL,NULL,&errmsg) < 0){
		printf("Sqlite3 create history fail!Reason:\n",errmsg);
		return -2;
	}else{
		printf("sqlite3 create  history success!\n");
	}
	return 0;
}

int info_get_count_opt()
{

	int row;
	int index;
	int column;
	char **result;
	sprintf(sql,"select count(*)id from employ_manager;");

	if(sqlite3_get_table(ppDb,sql,&result,&row,&column,&errmsg) < 0){
		printf("Select fail!Reason:%s\n",errmsg);
		return -1;
	}

	return atoi(result[1]);
}


int info_insert_opt(UserInfo *employ)
{
	sprintf(sql,"insert into employ_manager values('%s',%d,'%s',%d,'%c',%d,%d,%d,'%s');",\
			employ->department,employ->id,employ->name,employ->age,\
			employ->sex,employ->year,employ->salary,employ->managedr,employ->passwd);
	if(sqlite3_exec(ppDb,sql,NULL,NULL,&errmsg) < 0){
		printf("Insert fail!Reason:%s",errmsg);
		return -1;
	}else{
		printf("Insert success!\n");
		return 0;
	}
}

int info_delete_by_name_opt(char *name)
{
	sprintf(sql,"delete from employ_manager where name = '%s';",name);
	if(sqlite3_exec(ppDb,sql,NULL,NULL,&errmsg) < 0){
		printf("Delete fail!Reason :%s\n",errmsg);
		return -1;
	}else{
		printf("Delete success!\n");
		return 0;
	}
}

int info_find_by_name_opt(char *name,UserInfo *info)
{
	int i;
	int j;
	int row;
	int index;
	int column;
	char **result;

	sprintf(sql,"select * from employ_manager where name = '%s';",name);

	if(sqlite3_get_table(ppDb,sql,&result,&row,&column,&errmsg) < 0){
		printf("Select fail!Reason:%s\n",errmsg);
		return -1;
	}else{
			printf("Select success!\n");
	}

	if(column == 0)
	{
		return -1;
	}else{

		index = column;

		printf("index:%d\n",index);
		strcpy(info->department,result[index++]);
#if 1
		info[0].id =atoi(result[index++]);

		strcpy(info[0].name,result[index++]);
		info[0].age= atoi(result[index++]);
		info[0].sex =result[index++][0]; 
		info[0].year = atoi(result[index++]);
		info[0].salary=atoi(result[index++]); 
		info[0].managedr = atoi(result[index++]);
		strcpy(info[0].passwd,result[index++]);
#endif	
		printf("SQL manager = %d\n", info[0].managedr);
		printf("SQL name = %s\n", info[0].name);
		return 0;
	}
}


int info_update_by_name_opt(char * name,UserInfo *employ)
{
	sprintf(sql,"update employ_manager set department = '%s',id = %d,\
			age = %d,sex = '%c',year = %d,salary = %d,manager = %d,\
			passwd = '%s' where name = '%s';",\
			employ->department,employ->id,employ->age,employ->sex,\
			employ->year,employ->salary,employ->managedr,employ->passwd,name);
	if(sqlite3_exec(ppDb,sql,NULL,NULL,&errmsg) < 0){
		printf("Update fail!Reason:%s",errmsg);
		return -1;
	}else{
		printf("Update success!\n");
		return 0;
	}
}

int info_find_all_opt(UserInfo *info,int len)
{
	int i;
	int j;
	int row;
	int index;
	int column;
	char **result;

	sprintf(sql,"select * from employ_manager;");
	/*
	if(sqlite3_exec(ppDb,sql,NULL,NULL,&errmsg) < 0){
		printf("Find employ_manager all fail!Reason:%s\n",errmsg);
		return -1;
	}else{
		printf("Find employ_mansger all success!\n");
	}*/
	if(sqlite3_get_table(ppDb,sql,&result,&row,&column,&errmsg) < 0){
		printf("Select fail!Reason:%s\n",errmsg);
		return -1;
	}else{
			printf("Select success!\n");
	}

	index = column;

	for(i = 0; i < len; i++){
		strcpy(info[i].department,result[index++]);
		info[i].id =atoi(result[index++]);
		strcpy(info[i].name,result[index++]);
		info[i].age= atoi(result[index++]);
		info[i].sex =result[index++][0]; 
		info[i].year = atoi(result[index++]);
		info[i].salary=atoi(result[index++]); 
		info[i].managedr = atoi(result[index++]);
		strcpy(info[i].passwd,result[index++]);
	}
	return 0;
}

int log_find_all_opt(HistoryInfo *info,int len)
{
	int i;
	int j;
	int row;
	int index;
	int column;
	char **result;

	sprintf(sql,"select * from history;");
/*	if(sqlite3_exec(ppDb,sql,NULL,NULL,&errmsg)  < 0){
		printf("name find history fail! Reason :%s\n",errmsg);
		return -1;
	}else{
		printf("name find history success!\n");
	}
*/
	if(sqlite3_get_table(ppDb,sql,&result,&row,&column,&errmsg) < 0){
		printf("Select fail!Reason:%s\n",errmsg);
		return -1;
	}else{
			printf("Select success!\n");
	}

	index = column;

	for(i = 0; i < len; i++){
		strcpy(info[i].action,result[index++]);
		strcpy(info[i].date,result[index++]);
		strcpy(info[i].name,result[index++]);
	}
	return 0;
}

int log_get_count_opt(){ //得到历史记录的条数
	int row;
	int column;
	char **result;

	sprintf(sql,"select count(*)date from history;");
	if(sqlite3_get_table(ppDb,sql,&result,&row,&column,&errmsg) < 0){
		printf("Select fail!Reason:%s\n",errmsg);
		return -1;
	}else{
			printf("Select success!\n");
	}


	return atoi(result[1]);
}

int log_get_count_by_name_opt(char *name)
{
	int row;
	int column;
	char **result;
	sprintf(sql,"select count(*)date from history where name = '%s';",name);
	if(sqlite3_get_table(ppDb,sql,&result,&row,&column,&errmsg) < 0){
		printf("Select fail!Reason:%s\n",errmsg);
		return -1;
	}else{
			printf("Select success!\n");
	}
	
	return atoi(result[1]);

}

int log_find_by_name_opt(HistoryInfo *info,int len,char *name)

{
	int i;
	int j;
	int row;
	int index;
	int column;
	char **result;
	sprintf(sql,"select * from history where name = '%s';",name);
/*	if(sqlite3_exec(ppDb,sql,NULL,NULL,&errmsg)  < 0){
		printf("name find history fail! Reason :%s\n",errmsg);
		return -1;
	}else{
		printf("name find history success!\n");
	}*/

	if(sqlite3_get_table(ppDb,sql,&result,&row,&column,&errmsg) < 0){
		printf("Select fail!Reason:%s\n",errmsg);
		return -1;
	}else{
			printf("Select success!\n");
	}

	index = column;

	for(i = 0; i < len; i++){
		strcpy(info[i].action,result[index++]);
		strcpy(info[i].date,result[index++]);
		strcpy(info[i].name,result[index++]);
	}
	return 0;
}

int log_insert_opt(HistoryInfo *info)
{
	sprintf(sql,"insert into history values('%s','%s','%s');",info->action,info->date,info->name);
	if(sqlite3_exec(ppDb,sql,NULL,NULL,&errmsg)  < 0){
		printf("name find history fail! Reason :%s\n",errmsg);
		return -1;
	}else{
		printf("name find history success!\n");
		return 0;
	}
}


int log_delete_by_name_opt(char *name)
{
	sprintf(sql,"delete from history where name = '%s';",name);
	if(sqlite3_exec(ppDb,sql,NULL,NULL,&errmsg) < 0){
		printf("Delete fail!Reason :%s\n",errmsg);
		return -1;
	}else{
		printf("Delete success!\n");
		return 0;
	}
}

